using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Collections;
using System.Collections.Generic;
using dotnetOP.Base;
using dotnetOP.Attributes;

namespace dotnetOP.Data {
	/// <summary>
	/// Summary description for Class1.
	/// </summary>
	public class SqlGenerator {
		private string prefix = "dnop";
		private List<string> tablesGenerated = new List<string>();

        private List<StringWriter> sqlOutput = new List<StringWriter>();
        private string connectionString;
        
		public string Prefix {
			get {
				return prefix;
			}
			set {
				prefix = value;
			}
		}

        public SqlGenerator(string connectionString)
        {
            this.connectionString = connectionString;
        }

		public void InitializeDatabase(Type t) {
            if (!t.IsSubclassOf(typeof(DnopObject))) {
                throw new Exception(string.Format("The class {0} must inherit from DnopObject class.", t.Name));
            }
			tablesGenerated.Clear();
            sqlOutput.Clear();
            sqlOutput.Add(new StringWriter());

			generateDDL(t);
			sqlOutput[0].WriteLine("-- {0} tables generated.", tablesGenerated.Count);

            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();
            foreach (StringWriter sw in sqlOutput) {
                SqlCommand cmd = new SqlCommand(sw.ToString(), conn);
                cmd.ExecuteNonQuery();
            }
		}

        public List<string> GetInitializationScripts(Type t)
        {
            if (!t.IsSubclassOf(typeof(DnopObject))) {
                throw new Exception(string.Format("The class {0} must inherit from DnopObject class.", t.Name));
            }
            tablesGenerated.Clear();
            sqlOutput.Clear();
            sqlOutput.Add(new StringWriter());
            generateDDL(t);
            return sqlOutput.ConvertAll<string>(delegate(StringWriter sw) {
                return sw.ToString();
            });
        }

		private void generateDDL (Type t) {
            if (!t.IsSubclassOf(typeof(DnopObject))) {
                throw new Exception(string.Format("The class {0} must inherit from DnopObject class.", t.Name));
            }
            if (tablesGenerated.Contains(t.Name)) {
				return;
			}
			ArrayList references = new ArrayList();
			Dictionary<string, Type> multipleRefs = new Dictionary<string, Type>();
			if (t.GetFields().Length > 0) {
                sqlOutput[0].WriteLine("IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[{0}_{1}]') AND type in (N'U'))",
                    prefix, t.Name);
                sqlOutput[0].WriteLine("DROP TABLE [{0}_{1}]", prefix, t.Name);
				sqlOutput[0].WriteLine("CREATE TABLE [{0}_{1}] (\r\n[ID] INT PRIMARY KEY IDENTITY\r\n", prefix, t.Name);
				tablesGenerated.Add(t.Name);
			}
            int fieldIndex = 0;
			foreach (FieldInfo f in t.GetFields()) {
				if (f.FieldType.Equals(typeof(string))) {
                    object[] attributes = f.GetCustomAttributes(typeof(LengthAttribute), false);
                    if (attributes.Length > 0) {
                        LengthAttribute length = (LengthAttribute)attributes[0];
                        sqlOutput[0].Write(",[{0}] VARCHAR({1})", f.Name, length.Length);
                    } else {
                        sqlOutput[0].Write(",[{0}] TEXT", f.Name);
                    }
				} else if (f.FieldType.Equals(typeof(int))) {
					sqlOutput[0].Write(",[{0}] INT", f.Name);
				} else if (f.FieldType.Equals(typeof(long))) {
					sqlOutput[0].Write(",[{0}] BIGINT", f.Name);
				} else if (f.FieldType.Equals(typeof(float)) || f.FieldType.Equals(typeof(double))) {
					sqlOutput[0].Write(",[{0}] FLOAT", f.Name);
				} else if (f.FieldType.Equals(typeof(bool))) {
					sqlOutput[0].Write(",[{0}] BIT", f.Name);
                } else if (f.FieldType.Equals(typeof(DateTime))) {
                    sqlOutput[0].Write(",[{0}] DATETIME", f.Name);
				} else if (f.FieldType.IsArray) {
					multipleRefs.Add(f.Name, f.FieldType.GetElementType());
                } else if (f.FieldType.IsClass) {
                    sqlOutput[0].Write(",[{0}] INT", f.Name + "_ID");
                    if (!tablesGenerated.Contains(f.Name)) {
                        references.Add(f.FieldType);
                    }
                } else {
                    sqlOutput[0].Write(",[{0}] NTEXT", f.Name);
                }
                fieldIndex++;
			}
			sqlOutput[0].WriteLine("\r\n)\r\n");

            generateIndex(t);
            generateSaveProc(t);
            generateDeleteProc(t);
            generateGetProcs(t);
            generateSearchProc(t);

            foreach (string field in multipleRefs.Keys) {
				Type refType = multipleRefs[field];
				generateDDL(refType);
                sqlOutput[0].WriteLine("IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[{0}_{1}]') AND type in (N'U'))",
                    prefix, t.Name + "_" + field);
                sqlOutput[0].WriteLine("DROP TABLE [{0}_{1}_{2}]", prefix, t.Name, field);
                sqlOutput[0].WriteLine("CREATE TABLE [{0}_{1}_{2}] (", prefix, t.Name, field);
				sqlOutput[0].WriteLine("    {0}_ID INT NOT NULL,", t.Name);
				sqlOutput[0].WriteLine("    {0}_ID INT NOT NULL", refType.Name);
				sqlOutput[0].WriteLine(")\r\n");
				sqlOutput[0].WriteLine("ALTER TABLE [{0}_{1}_{2}] ADD CONSTRAINT PK_{0}_{1}_{2} PRIMARY KEY(", prefix, t.Name, field);
				sqlOutput[0].WriteLine("    {0}_ID, {1}_ID", t.Name, refType.Name);
				sqlOutput[0].WriteLine(")\r\n");

                generateReferenceProc(t.Name, field, refType.Name);
			}
			foreach (Type refType in references) {
				generateDDL(refType);
			}
		}

        private void generateIndex(Type t) {
            Dictionary<string, string> index = new Dictionary<string, string>();
            foreach (FieldInfo f in t.GetFields()) {
                object[] attributes = f.GetCustomAttributes(typeof(IndexAttribute), false);
                if (attributes.Length > 0) {
                    IndexAttribute attr = (IndexAttribute)attributes[0];
                    index[f.Name] = attr.Sorting.ToString();
                }
            }
            if (index.Count > 0) {
                sqlOutput[0].WriteLine(
                    @"CREATE NONCLUSTERED INDEX [IX_" + prefix + @"_" + t.Name + @"] ON [" + prefix + @"_" + t.Name + @"] 
                        (");
                int i = 0;
                foreach (string k in index.Keys) {
                    if (i > 0) {
                        sqlOutput[0].Write(", ");
                    }
                    sqlOutput[0].WriteLine("[" + k + "] " + index[k]);
                    i++;
                }
                sqlOutput[0].WriteLine(") ON [PRIMARY]");
            }
        }

        private void generateSaveProc(Type t) {
            sqlOutput[0].WriteLine(@"IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[{0}_Save{1}]') AND type in (N'P', N'PC'))
                DROP PROCEDURE [{0}_Save{1}]", prefix, t.Name);
            StringWriter output = new StringWriter();
            sqlOutput.Add(output);
            output.WriteLine("CREATE PROCEDURE [{0}_Save{1}]\r\n@ID INT,", prefix, t.Name);
            List<string> parameters = new List<string>();
            List<string> set = new List<string>();
            List<string> insert = new List<string>();
            List<string> values = new List<string>();
            foreach (FieldInfo f in t.GetFields()) {
                if (f.FieldType.Equals(typeof(string)) || f.FieldType.Equals(typeof(int)) || f.FieldType.Equals(typeof(long)) ||
                    f.FieldType.Equals(typeof(float)) || f.FieldType.Equals(typeof(double)) || f.FieldType.Equals(typeof(bool)) ||
                    f.FieldType.Equals(typeof(DateTime))) {
                    set.Add(string.Format("[{0}] = @{0}", f.Name));
                    insert.Add(f.Name);
                    values.Add(string.Format("@{0}", f.Name));
                } else if (f.FieldType.IsClass && !f.FieldType.IsArray) {
                    set.Add(string.Format("[{0}_ID] = @{0}_ID", f.Name));
                    insert.Add(string.Format("{0}_ID", f.Name));
                    values.Add(string.Format("@{0}_ID", f.Name));
                }
                if (f.FieldType.Equals(typeof(string))) {
                    object[] attributes = f.GetCustomAttributes(typeof(LengthAttribute), false);
                    if (attributes.Length > 0) {
                        LengthAttribute length = (LengthAttribute)attributes[0];
                        parameters.Add(string.Format("@{0} VARCHAR({1})", f.Name, length.Length));
                    } else {
                        parameters.Add(string.Format("@{0} TEXT", f.Name));
                    }
                } else if (f.FieldType.Equals(typeof(int))) {
                    parameters.Add(string.Format("@{0} INT", f.Name));
                } else if (f.FieldType.Equals(typeof(long))) {
                    parameters.Add(string.Format("@{0} BIGINT", f.Name));
                } else if (f.FieldType.Equals(typeof(float)) || f.FieldType.Equals(typeof(double))) {
                    parameters.Add(string.Format("@{0} FLOAT", f.Name));
                } else if (f.FieldType.Equals(typeof(bool))) {
                    parameters.Add(string.Format("@{0} BIT", f.Name));
                } else if (f.FieldType.Equals(typeof(DateTime))) {
                    parameters.Add(string.Format("@{0} DATETIME", f.Name));
                } else if (f.FieldType.IsClass && !f.FieldType.IsArray) {
                    parameters.Add(string.Format("@{0}_ID INT", f.Name));
                }
            }
            output.WriteLine(string.Join(",\r\n", parameters.ToArray()));
            output.WriteLine("AS");
            output.WriteLine("BEGIN");
            output.WriteLine("\tSET NOCOUNT ON;");

            output.WriteLine("\tUPDATE {0}_{1} SET\r\n\t\t{2}", prefix, t.Name, string.Join(", ", set.ToArray()));
            output.WriteLine("\tWHERE ID = @ID");
            output.WriteLine("\tIF @@ROWCOUNT = 0\r\n\tBEGIN");
            output.WriteLine("\t\tINSERT INTO {0}_{1} ({2})", prefix, t.Name, string.Join(", ", insert.ToArray()));
            output.WriteLine("\t\tVALUES ({0})", string.Join(", ", values.ToArray()));
            output.WriteLine("\t\tSELECT @@IDENTITY\r\n\tEND");
            output.WriteLine("\tELSE\r\n\t\tSELECT @ID");
            
            output.WriteLine("END");
        }

        private void generateReferenceProc(string typeName, string refFieldName, string refTypeName) {
            sqlOutput[0].WriteLine(@"IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[{0}_SaveRef{1}_{2}]') AND type in (N'P', N'PC'))
                DROP PROCEDURE [{0}_SaveRef{1}_{2}]", prefix, typeName, refFieldName);
            sqlOutput[0].WriteLine(@"IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[{0}_DeleteRef{1}_{2}]') AND type in (N'P', N'PC'))
                DROP PROCEDURE [{0}_DeleteRef{1}_{2}]", prefix, typeName, refFieldName);
            StringWriter output = new StringWriter();
            sqlOutput.Add(output);
            output.WriteLine("CREATE PROCEDURE [{0}_SaveRef{1}_{2}]", prefix, typeName, refFieldName);
            output.WriteLine("\t@{0}_ID INT,", typeName);
            output.WriteLine("\t@{0}_ID INT", refTypeName);
            output.WriteLine("AS\r\nBEGIN");
            output.WriteLine("\tINSERT INTO [{0}_{1}_{2}] ({1}_ID, {3}_ID)", prefix, typeName, refFieldName, refTypeName);
            output.WriteLine("\tVALUES(@{0}_ID, @{1}_ID)\r\nEND", typeName, refTypeName);

            output = new StringWriter();
            sqlOutput.Add(output);
            output.WriteLine("CREATE PROCEDURE [{0}_DeleteRef{1}_{2}]", prefix, typeName, refFieldName);
            output.WriteLine("\t@{0}_ID INT,", typeName);
            output.WriteLine("\t@{0}_ID INT", refTypeName);
            output.WriteLine("AS\r\nBEGIN");
            output.WriteLine("\tDELETE FROM [{0}_{1}_{2}]", prefix, typeName, refFieldName, refTypeName);
            output.WriteLine("\tWHERE {0}_ID = @{0}_ID AND {1}_ID = @{1}_ID\r\nEND", typeName, refTypeName);
        }

        private void generateDeleteProc(Type t) {
            sqlOutput[0].WriteLine(@"IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[{0}_Delete{1}]') AND type in (N'P', N'PC'))
                DROP PROCEDURE [{0}_Delete{1}]", prefix, t.Name);
            StringWriter output = new StringWriter();
            sqlOutput.Add(output);
            output.WriteLine("CREATE PROCEDURE [{0}_Delete{1}]\r\n@ID INT", prefix, t.Name);
            output.WriteLine("AS\r\nBEGIN");
            output.WriteLine("\tDELETE FROM {0}_{1}\r\n\tWHERE ID = @ID\r\nEND", prefix, t.Name);
        }

        private void generateGetProcs(Type t) {
            sqlOutput[0].WriteLine(@"IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[{0}_Get{1}ByID]') AND type in (N'P', N'PC'))
                DROP PROCEDURE [{0}_Get{1}ByID]", prefix, t.Name);
            StringWriter output = new StringWriter();
            sqlOutput.Add(output);
            output.WriteLine("CREATE PROCEDURE [{0}_Get{1}ByID]\r\n\t@ID INT\r\nAS\r\nBEGIN", prefix, t.Name);
            output.WriteLine("\tSELECT * FROM {0}_{1}\r\n\tWHERE ID = @ID\r\nEND", prefix, t.Name);

            foreach (FieldInfo fieldInfo in t.GetFields()) {
                if (fieldInfo.FieldType.IsArray) {
                    sqlOutput[0].WriteLine(@"IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[{0}_GetRef{1}_{2}]') AND type in (N'P', N'PC'))
                        DROP PROCEDURE [{0}_GetRef{1}_{2}]", prefix, t.Name, fieldInfo.Name);

                    output = new StringWriter();
                    sqlOutput.Add(output);
                    output.WriteLine("CREATE PROCEDURE [{0}_GetRef{1}_{2}]\r\n\t@{1}_ID INT\r\nAS\r\nBEGIN", prefix, t.Name, fieldInfo.Name);
                    output.WriteLine("\tSELECT * FROM {0}_{1}_{2}\r\n\tWHERE {1}_ID = @{1}_ID\r\nEND", prefix, t.Name, fieldInfo.Name);
                }
            }
        }

        private void generateSearchProc(Type t) {
            sqlOutput[0].WriteLine(@"IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[{0}_Search{1}]') AND type in (N'P', N'PC'))
                DROP PROCEDURE [{0}_Search{1}]", prefix, t.Name);
            StringWriter output = new StringWriter();
            sqlOutput.Add(output);
            output.WriteLine("CREATE PROCEDURE [{0}_Search{1}]\r\n", prefix, t.Name);
            List<string> parameters = new List<string>();
            List<string> where = new List<string>();
            foreach (FieldInfo f in t.GetFields()) {
                object[] attributes = f.GetCustomAttributes(typeof(IndexAttribute), false);
                if (attributes.Length > 0) {
                    if (f.FieldType.Equals(typeof(string)) || f.FieldType.Equals(typeof(int)) || f.FieldType.Equals(typeof(long)) ||
                        f.FieldType.Equals(typeof(float)) || f.FieldType.Equals(typeof(double)) || f.FieldType.Equals(typeof(bool)) ||
                        f.FieldType.Equals(typeof(DateTime))) {
                        where.Add(string.Format("{0} = @{0}", f.Name));
                    }
                    if (f.FieldType.Equals(typeof(string))) {
                        attributes = f.GetCustomAttributes(typeof(LengthAttribute), false);
                        if (attributes.Length > 0) {
                            LengthAttribute length = (LengthAttribute)attributes[0];
                            parameters.Add(string.Format("@{0} VARCHAR({1})", f.Name, length.Length));
                        } else {
                            parameters.Add(string.Format("@{0} TEXT", f.Name));
                        }
                    } else if (f.FieldType.Equals(typeof(int))) {
                        parameters.Add(string.Format("@{0} INT", f.Name));
                    } else if (f.FieldType.Equals(typeof(long))) {
                        parameters.Add(string.Format("@{0} BIGINT", f.Name));
                    } else if (f.FieldType.Equals(typeof(float)) || f.FieldType.Equals(typeof(double))) {
                        parameters.Add(string.Format("@{0} FLOAT", f.Name));
                    } else if (f.FieldType.Equals(typeof(bool))) {
                        parameters.Add(string.Format("@{0} BIT", f.Name));
                    } else if (f.FieldType.Equals(typeof(DateTime))) {
                        parameters.Add(string.Format("@{0} DATETIME", f.Name));
                    }
                }
            }
            output.WriteLine(string.Join(",\r\n", parameters.ToArray()));
            output.WriteLine("AS\r\nBEGIN");
            output.WriteLine("\tSELECT * FROM {0}_{1}", prefix, t.Name);
            if (where.Count > 0) {
                output.WriteLine("\tWHERE {0}", string.Join(" AND ", where.ToArray()));
            }
            output.WriteLine("END");
        }
    }
}
